#include "db.h"
#include <QSqlDatabase>
#include <QMessageBox>
#include "tool.h"
#include <QString>
#include <QDebug>
#include <QCryptographicHash>


bool Db::testDb(const QString &ip,const QString &port,const QString &name,const QString &user,const QString &password){

    QSqlDatabase db;
    if (QSqlDatabase::contains("test"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("test");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "test");
    db.setHostName(ip);         //主机名
    db.setPort(port.toInt());           //端口
    db.setDatabaseName(name);          //数据库名
    db.setUserName(user);              //用户名
    db.setPassword(password);            //密码

    if(db.open()){
        db.close();
        return true;
    }else{
        db.close();
        return false;
    }
    db.close();
}

QStringList Db::getCategoryName()
{
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));//密码
    db.open();
    QSqlQuery result = db.exec("select cname from category");
    db.close();
    QStringList res;
    while(result.next()){
        res.append(result.value("cname").toString());
    }
    return res;
}

int Db::getCategoryID(const QString &cname)
{
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));//密码
    db.open();
    QSqlQuery result = db.exec(QString("select cid from category where cname = '%1'").arg(cname));
    if(result.next())
    {
        return result.value("cid").toInt();
    }
    else
    {
        return -1;
    }
}

QSqlQuery Db::getProductNameAndCno()
{
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));//密码
    db.open();
    QSqlQuery result = db.exec("select pname, cno from product");
    db.close();
    return result;
}

QSqlQuery Db::getProductIfm(const QString &pname){
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));//密码
    db.open();
    QString sql = QString("SELECT pid, pname, picture_address, pdesc, position, price, cid, cname FROM product p RIGHT JOIN category c ON p.cno = c.cid WHERE pname = '%1'").arg(pname);
    QSqlQuery result = db.exec(sql);
    db.close();
    return result;
}

QSqlQuery Db::getUserIfm(const QString &uname){
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("SELECT uid, username, password, ulevel, salt FROM ad_user WHERE username = '%1'").arg(uname);
    QSqlQuery result = db.exec(sql);
    db.close();
    return result;
}

bool Db::insertProduct(const QString &pname, const QString &paddress, const QString &pdesc, const QString &position, double price, int cno)
{
    if(getProductIfm(pname).next()) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("INSERT INTO product(pname, picture_address, pdesc, position, price, cno) VALUES('%1', '%2', '%3', '%4', %5, %6)").arg(pname, paddress, pdesc, position, QString::number(price), QString::number(cno));
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::insertCategory(const QString &cname, const QString &cdesc)
{
    if (getCategoryID(cname) != -1) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("INSERT INTO category(cname, cdesc) VALUES('%1', '%2')").arg(cname, cdesc);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::deleteProduct(const QString &pname)
{
    if(!getProductIfm(pname).next()) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("delete from product where pname = '%1'").arg(pname);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::deleteCategory(const QString &cname)
{
    if (getCategoryID(cname) == -1) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("delete from category where cname = '%1'").arg(cname);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::changePassword(const QString &uname, const QString &oldpwd, const QString &newpwd)
{
    QSqlQuery ifm = Db::getUserIfm(uname);
    QString salt = "";
    if(ifm.next())
        salt = ifm.value("salt").toString();
    else
        return false;
    QString oldpwdn = salt + oldpwd;
    QString oldpwdmd5 = QCryptographicHash::hash( oldpwdn.toLatin1(), QCryptographicHash::Md5 ).toHex();
    if(ifm.value("password").toString() != oldpwdmd5) return false;
    QString newpwdn = salt + newpwd;
    QString newpwdmd5 = QCryptographicHash::hash( newpwdn.toLatin1(), QCryptographicHash::Md5 ).toHex();
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("UPDATE ad_user SET password='%1' WHERE username = '%2'").arg(newpwdmd5, uname);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::insertUser(const QString &uname, const QString &pwd)
{
    QString salt = QCryptographicHash::hash( Tool::RandomQString().toLatin1(), QCryptographicHash::Md5 ).toHex().left(20);
    QString pwdn = salt + pwd;
    QString pwdmd5 = QCryptographicHash::hash( pwdn.toLatin1(), QCryptographicHash::Md5 ).toHex();
    if (Db::getUserIfm(uname).next()) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("INSERT INTO ad_user(username, password, ulevel, salt) VALUES('%1', '%2', %3, '%4')").arg(uname, pwdmd5, "1" , salt);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}

bool Db::deleteUser(const QString &uname)
{
    if (!Db::getUserIfm(uname).next()) return false;
    QSqlDatabase db;
    if (QSqlDatabase::contains("shop"))  //连接的MYSQL的数据库驱动
        db = QSqlDatabase::database("shop");
    else
        db = QSqlDatabase::addDatabase("QMYSQL", "shop");
    db.setHostName(Tool::fileRead("config/ipconfig.ini"));         //主机名
    db.setPort(Tool::fileRead("config/portconfig.ini").toInt());           //端口
    db.setDatabaseName(Tool::fileRead("config/nameconfig.ini"));          //数据库名
    db.setUserName(Tool::fileRead("config/userconfig.ini"));              //用户名
    db.setPassword(Tool::toUpperAndLower(QByteArray::fromBase64(Tool::fileRead("config/pwdconfig.ini").toLocal8Bit())));       //密码
    db.open();
    QString sql = QString("delete from ad_user where username = '%1'").arg(uname);
    QSqlQuery query(db);
    bool f = query.exec(sql);
    db.close();
    return f;
}
